﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataRow Convert2GPBST(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryGPBST(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    dwReturn["GPBMANDANT"] = "00";
                    dwReturn["GPBPARTIE"] = "0002956";
                    dwReturn["GPBPARTIEKZ"] = "0";
                    dwReturn["GPBLO"] = "01";
                    dwReturn["GPBLB"] = "CP";
                    dwReturn["GPBAENDZL"] = "0";
                    dwReturn["GPBAENDBEN"] = "        ";
                    dwReturn["GPBAENDDAT"] = "0";
                    dwReturn["GPBNEUBEN"] = "JAKV    ";
                    dwReturn["GPBNEUDAT"] = "20150108";
                    dwReturn["GPBART"] = "45021.NE.20.1       ";
                    dwReturn["GPBVAR"] = "   ";
                    dwReturn["GPBFARBE"] = "Y8581S    ";
                    dwReturn["GPBDEZI"] = " ";
                    dwReturn["GPBBSTKGG"] = "1120000";
                    dwReturn["GPBBSTKAG"] = "56";
                    dwReturn["GPBBSTSPG"] = "0";
                    dwReturn["GPBBSTKGZ"] = "0";
                    dwReturn["GPBBSTKAZ"] = "0";
                    dwReturn["GPBBSTSPZ"] = "0";
                    dwReturn["GPBEINDAT"] = "20141229";
                    dwReturn["GPBSPPART"] = "          ";
                    dwReturn["GPBZWPART"] = "          ";
                    dwReturn["GPBFILLER"] = "                            ";

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }

        public void SaveGPBST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGPBST(dwData, 1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapGPBST(ref dwLocal, dwData);
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    if (!flgExist)
                    {
                        InsertGPBST(dwLocal, 1);
                        UpdateGPBST(dwLocal, 1);
                    }
                    else
                    {
                        UpdateGPBST(dwLocal, 3); 
                    } 
                    break;
            }
        }
        public void MapGPBST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public DataSet QueryGPBST(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            // GSCMANDANT GSCART GSCVAR GSCFARBE


            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM GPBST WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM GPBST WHERE GPBLO=:GPBLO AND GPBLB=:GPBLB AND GPBMANDANT=:GPBMANDANT AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ ";
                    or1.Parameters.Add("GPBMANDANT", OracleType.NVarChar).Value = dwData["GPBMANDANT"].ToString();
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM GPBST WHERE GPBLO=:GPBLO AND GPBLB=:GPBLB AND GPBMANDANT=:GPBMANDANT AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ ";
                    or1.Parameters.Add("GPBMANDANT", OracleType.NVarChar).Value = dwData["ZUMANDANT"].ToString();
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["ZULO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["ZULB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["ZUPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["ZUPARTIEKZ"].ToString();
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "GPBST";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public void InsertGPBST(DataRow dwData, int option = 1)
        {

            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO GPBST(GPBMANDANT, GPBPARTIE, GPBPARTIEKZ, GPBLO, GPBLB, GPBAENDZL, GPBAENDBEN, GPBAENDDAT, GPBNEUBEN, GPBNEUDAT, GPBART, GPBVAR, GPBFARBE, GPBDEZI, GPBBSTKGG, GPBBSTKAG, GPBBSTSPG, GPBBSTKGZ, GPBBSTKAZ, GPBBSTSPZ, GPBEINDAT, GPBSPPART, GPBZWPART, GPBFILLER)"
                    + " VALUES (:GPBMANDANT, :GPBPARTIE, :GPBPARTIEKZ,:GPBLO,:GPBLB, 1, 'INTEXE  ', 20150112, 'INTEXE  ', 20150112, '40021.NE.16.1       ', '   ', 'YB038-2   ', ' ', 9550, 1, 100, 0, 0, 0, 20141020, 'H140161012', '.         ', '                            ')";
                    or1.Parameters.Add("GPBMANDANT", OracleType.NVarChar).Value = dwData["GPBMANDANT"];
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"];
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"];
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"];
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"];
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public void UpdateGPBST(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE GPBST SET "
                    + "GPBMANDANT=:GPBMANDANT,"
                    + "GPBPARTIE=:GPBPARTIE,"
                    + "GPBPARTIEKZ=:GPBPARTIEKZ,"
                    + "GPBLO=:GPBLO,"
                    + "GPBLB=:GPBLB,"
                    + "GPBAENDZL=:GPBAENDZL,"
                    + "GPBAENDBEN=:GPBAENDBEN,"
                    + "GPBAENDDAT=:GPBAENDDAT,"
                    + "GPBNEUBEN=:GPBNEUBEN,"
                    + "GPBNEUDAT=:GPBNEUDAT,"
                    + "GPBART=:GPBART,"
                    + "GPBVAR=:GPBVAR,"
                    + "GPBFARBE=:GPBFARBE,"
                    + "GPBDEZI=:GPBDEZI,"
                    + "GPBBSTKGG=:GPBBSTKGG,"
                    + "GPBBSTKAG=:GPBBSTKAG,"
                    + "GPBBSTSPG=:GPBBSTSPG,"
                    + "GPBBSTKGZ=:GPBBSTKGZ,"
                    + "GPBBSTKAZ=:GPBBSTKAZ,"
                    + "GPBBSTSPZ=:GPBBSTSPZ,"
                    + "GPBEINDAT=:GPBEINDAT,"
                    + "GPBSPPART=:GPBSPPART,"
                    + "GPBZWPART=:GPBZWPART,"
                    + "GPBFILLER=:GPBFILLER "
                 + " WHERE  "
                 + "  GPBMANDANT=:GPBMANDANT AND GPBPARTIE=:GPBPARTIE  AND GPBPARTIEKZ=:GPBPARTIEKZ AND GPBLO=:GPBLO AND GPBLB=:GPBLB";

                    or1.Parameters.Add("GPBMANDANT", OracleType.NVarChar).Value = dwData["GPBMANDANT"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBAENDZL", OracleType.NVarChar).Value = dwData["GPBAENDZL"].ToString();
                    or1.Parameters.Add("GPBAENDBEN", OracleType.NVarChar).Value = dwData["GPBAENDBEN"].ToString();
                    or1.Parameters.Add("GPBAENDDAT", OracleType.NVarChar).Value = dwData["GPBAENDDAT"].ToString();
                    or1.Parameters.Add("GPBNEUBEN", OracleType.NVarChar).Value = dwData["GPBNEUBEN"].ToString();
                    or1.Parameters.Add("GPBNEUDAT", OracleType.NVarChar).Value = dwData["GPBNEUDAT"].ToString();
                    or1.Parameters.Add("GPBART", OracleType.NVarChar).Value = dwData["GPBART"].ToString();
                    or1.Parameters.Add("GPBVAR", OracleType.NVarChar).Value = dwData["GPBVAR"].ToString();
                    or1.Parameters.Add("GPBFARBE", OracleType.NVarChar).Value = dwData["GPBFARBE"].ToString();
                    or1.Parameters.Add("GPBDEZI", OracleType.NVarChar).Value = dwData["GPBDEZI"].ToString();
                    or1.Parameters.Add("GPBBSTKGG", OracleType.NVarChar).Value = dwData["GPBBSTKGG"].ToString();
                    or1.Parameters.Add("GPBBSTKAG", OracleType.NVarChar).Value = dwData["GPBBSTKAG"].ToString();
                    or1.Parameters.Add("GPBBSTSPG", OracleType.NVarChar).Value = dwData["GPBBSTSPG"].ToString();
                    or1.Parameters.Add("GPBBSTKGZ", OracleType.NVarChar).Value = dwData["GPBBSTKGZ"].ToString();
                    or1.Parameters.Add("GPBBSTKAZ", OracleType.NVarChar).Value = dwData["GPBBSTKAZ"].ToString();
                    or1.Parameters.Add("GPBBSTSPZ", OracleType.NVarChar).Value = dwData["GPBBSTSPZ"].ToString();
                    or1.Parameters.Add("GPBEINDAT", OracleType.NVarChar).Value = dwData["GPBEINDAT"].ToString();
                    or1.Parameters.Add("GPBSPPART", OracleType.NVarChar).Value = dwData["GPBSPPART"].ToString();
                    or1.Parameters.Add("GPBZWPART", OracleType.NVarChar).Value = dwData["GPBZWPART"].ToString();
                    or1.Parameters.Add("GPBFILLER", OracleType.NVarChar).Value = dwData["GPBFILLER"].ToString();
                    Connect2Proxy(or1);
                    break;
                case 2:
                    or1.CommandText = "UPDATE GPBST SET "
                        + "GPBBSTKGZ=:GPBBSTKGZ "
                        + " WHERE  GPBLO=:GPBLO AND GPBLB=:GPBLB AND  GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GLPARTIEKZ";

                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    or1.Parameters.Add("GPBBSTKGZ", OracleType.NVarChar).Value = dwData["GPBBSTKGZ"].ToString();
                    Connect2Proxy(or1);
                    break;
                case 3:
                    or1.CommandText = "UPDATE GPBST SET (GPBBSTKGG,GPBBSTKAG,GPBBSTSPG)=(SELECT nvl(SUM(GLBSTKG),0),nvl(SUM(GLBSTKA),0),NVL(SUM(GLBSTSP),0) FROM GLLG"
                        + " WHERE  GPBMANDANT=GLMANDANT AND GPBPARTIE=GLPARTIE AND GPBPARTIEKZ=GLPARTIEKZ AND GPBLO=GLLO AND GPBLB=GLLB)"
                        + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ AND GPBLO=:GPBLO AND GPBLB=:GPBLB ";
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    Connect2Proxy(or1);
                    UpdateGPBST(dwData, 4);
                    break;
                case 4:
                    or1.CommandText = "UPDATE GPBST SET (GPBBSTKGZ,GPBBSTKAZ,GPBBSTSPZ)=(SELECT NVL(SUM(ZUEINTKG),0),NVL(SUM(ZUEINTKA),0),NVL(SUM(ZUEINTSP),0) FROM ZUTEI"
                        + " WHERE  GPBMANDANT=ZUMANDANT AND GPBPARTIE=ZUPARTIE AND GPBPARTIEKZ=ZUPARTIEKZ AND GPBLO=ZULO AND GPBLB=ZULB)"
                        + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ AND GPBLO=:GPBLO AND GPBLB=:GPBLB ";
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    Connect2Proxy(or1);
                    break;

            }

        }
        public void DeleteGPBST(DataRow dwData, int option = 1)
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 2:
                    or1.CommandText = "DELETE  GPBST"
                         + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ AND GPBLO=:GPBLO AND GPBLB=:GPBLB "
                        +"  AND NOT EXISTS (SELECT 1 FROM GLLG WHERE  GPBMANDANT=GLMANDANT AND GPBPARTIE=GLPARTIE AND GPBPARTIEKZ=GLPARTIEKZ AND GPBLO=GLLO AND GPBLB=GLLB)";
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }        
    }
}
